package warehouse;

import genetic2.ColumnStatistics;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import warehouse.Util.DBDataType;

/**
 *
 * @author Manolescu Razvan
 */

public class Persistance {
     private static Connection con;
    
     private static Constants tipAcces = null;
     private static String datamartSelectStr = "select datamart_id, datamart_name, GROUP_CONCAT(table_name) from warehouse.datamarts GROUP BY datamart_id HAVING GROUP_CONCAT(table_name) LIKE '";
     
     public Persistance(){
         
         try{
            Class.forName("com.mysql.jdbc.Driver");
            con =  DriverManager.getConnection("jdbc:mysql://"+Constants.ipServer+"/"+Constants.DB_NAME,
                    Constants.rootID, Constants.rootPassword);
         }catch(Exception e){
             if( Constants.debug )
                System.out.println("[Eroare] Eroare conexiune BD" + e.getMessage());
         }
        }

     public ResultSet getAllFromTable(String table){
         return getAllFromTable(table, null);
     }

     /**
      * Get all values from table
      * 
      * @param table Table name
      * @param conditions conditions array  Ex new String[]{" col1 = value1 "};
      * @return ResultSet
      * 
      */
     public ResultSet getAllExecuteQuery(String query){
         ResultSet rs = null;
         Statement s = null;
         try {
             s = con.createStatement();
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
             if(Constants.debug)
                 System.out.println("Execute Query "+query);
         try {             
             rs = s.executeQuery(query);
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
         
         return rs;  
     }
     
     public Collection<DatamartInfo> getDatamartCache() throws Exception{
         ResultSet rs = null;
         Statement s = null;
         try {
             s = con.createStatement();
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
        final Map<String, DatamartInfo> map = new HashMap<String, DatamartInfo>();
         try {             
             rs = s.executeQuery("SELECT * FROM datamarts;");
         
             

             rs.beforeFirst();

             while(rs.next()){
                 final String datamartName = rs.getString(3);
                 if(map.get(datamartName) == null){
                     final DatamartInfo di = new DatamartInfo(datamartName);
                     map.put(di.name, di);
                 }

                 final Map<String, List<String>> tableColumn = map.get(datamartName).tableColumn;
                 final List<String> colLit = new ArrayList<String>();
                 final String colsDb = rs.getString(4);
                 final String tableDb = rs.getString(2);
                 if(colsDb != null)
                 {
                    colLit.addAll(Arrays.asList(colsDb.split(",")));
                 }

                 tableColumn.put(tableDb, colLit);
             }
         
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
         finally{
            try {
                s.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
         }
         
         return map.values();
     }
    
     //TODO : make this generic
    public List<Integer> getIntValues(final String table, final String column){
         ResultSet rs = null;
         Statement s = null;
         final List<Integer> values = new ArrayList<Integer>();
         try {
             s = con.createStatement();
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }

         try {             
             rs = s.executeQuery("SELECT "+column+" FROM " + table +" ;");
             rs.beforeFirst();
             
             while(rs.next()){
                 values.add(rs.getInt(1));
             }
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
         finally{
            try {
                s.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
         }
         
         return values;
     }
     
    public List<String> getTablesFromDW(){
         ResultSet rs = null;
         Statement s = null;
         final List<String> tableNames = new ArrayList<String>();
         try {
             s = con.createStatement();
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }

         try {             
             rs = s.executeQuery("SHOW TABLES FROM " + Constants.DB_NAME);
             rs.beforeFirst();
             
             while(rs.next()){
                 tableNames.add(rs.getString(1));
             }
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
         finally{
            try {
                s.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
         }
         
         return tableNames;
     }
    
     public List<ColumnStatistics> getTableStatistics(final String tableName){
         ResultSet rs = null;
         Statement s = null;
         final List<ColumnStatistics> columnInfo = new ArrayList<ColumnStatistics>();
         try {
             s = con.createStatement();
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }

         try {             
             rs = s.executeQuery("SHOW COLUMNS FROM " + tableName + ";");
             rs.beforeFirst();
             
             while(rs.next()){
                 ColumnStatistics info = new ColumnStatistics();
                 info.name = rs.getString(1);
                 info.dataType = DBDataType.fromStringFuzzy(rs.getString(2));
                 info.length = DBDataType.getLengthFromDB(rs.getString(2));
                 columnInfo.add(info);
             }
             
             rs = s.executeQuery("SELECT COUNT(1) FROM " + tableName + ";");
             rs.beforeFirst(); 
             rs.next();
             final int count = rs.getInt(1);
             
             for(ColumnStatistics statistics : columnInfo){
                 statistics.fullCount = count;
                 statistics.count = count;
                 if(statistics.length > 1){
                     // VARCHAR
                     statistics.sizeInB = count * DBDataType.getVarcharSize(statistics.length) ;
                 }
                 else {
                    statistics.sizeInB = count * statistics.dataType.getSizeInB();
                 }
             }
             
         } catch (SQLException ex) {
             Logger.getLogger(Persistance.class.getName()).log(Level.SEVERE, null, ex);
         }
         finally{
            try {
                s.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
         }
         
         return columnInfo;
     }
    
     public ResultSet getAllFromTable(String table, String[] conditions){
        Statement s  = null;
        ResultSet rs = null;
        try {
             StringBuilder sb = new StringBuilder("SELECT * FROM ");
             sb.append(table);
             sb.append(" ");
             
             if(conditions != null && conditions.length > 0){
                 sb.append("WHERE ");
                 for(int i = 0; i<conditions.length; i++){
                     sb.append(conditions[i]);
                     sb.append(" ");
                     if(i < conditions.length - 1){
                          sb.append("AND ");
                     }
                 }
             }
             sb.append(";");
             s = con.createStatement();
             if(Constants.debug)
                 System.out.println("Execute Query "+sb.toString());
             
             rs = s.executeQuery(sb.toString());
             
        } catch (Exception ex) {        
            System.out.println("[ERROR] "+ex.getMessage());
        }
        /*finally{
            if(s != null){
                try {
                   s.close();
                } catch (SQLException ex) {                
                }
            }
        }*/
         
        return rs;
     }
     
     public void createDataMart(String dataMartName, Map<String, List<String>> map, String query){
        Statement s  = null;
        Statement s2  = null;

        ResultSet rs = null;
        List<String[]> datamartData = null;
        try {
             s = con.createStatement();
             s2 = con.createStatement();
             
             StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS `");
             sb.append(dataMartName);
             sb.append("` AS (");
             sb.append(query);
             sb.append(");");
             if(Constants.debug)
                 System.out.println("Execute Update "+sb.toString());
            int updated = s.executeUpdate(sb.toString());
            if(updated == 0)
                return;
            
            String update = "INSERT INTO datamarts VALUES(%s,'%s','%s','%s');";
            
            rs = s2.executeQuery("SELECT MAX(datamart_id) from datamarts;");
            rs.beforeFirst();
            rs.next();
            int id = rs.getInt(1);
            
            for(String table : map.keySet()){
                final StringBuilder colList = new StringBuilder();
                if(table == null)
                    continue;
                if(map.get(table) != null)
                    for(String col : map.get(table)){
                        colList.append(col);
                        colList.append(",");
                    }
                
                s.executeUpdate(String.format(update, ++id, table, dataMartName, colList));
            } 
            
            rs.close();
        } catch (Exception ex) {        
            System.out.println("[ERROR] "+ex.getMessage());
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
            if(s2 != null){
                try {
                    s2.close();
                } catch (SQLException ex) {                
                }
            }
        } 
     }
     
     public ColumnTree buildTableTree(final String factTable) throws SQLException{
        final List<String> tables = new ArrayList<String>();
        ResultSet rs = null;
        final Map<String, TableNode> tableMap = new HashMap<String, TableNode>();
        
        final DatabaseMetaData meta = con.getMetaData();

         rs = con.createStatement().executeQuery("Show tables");
              
         while (rs.next()) 
             tables.add(rs.getString(1));
         
         for(String table : tables){
             if(table.startsWith(Constants.DATAMART_PREFIX))
                 continue;
             
             TableNode t = tableMap.get(table);
             if(t == null){
                t = new TableNode(table);
                tableMap.put(table, t);
             }
             
             final Map<String, Integer> columns = getTableColumns(table);
             t.columnNamesQueryMap = columns; 
                        
             rs = meta.getExportedKeys(con.getCatalog(), null, table);
             while (rs.next()) {
                 
                //System.out.println("FK "+table+"-> "+ rs.getString("FKTABLE_NAME") +"."+ rs.getString("FKCOLUMN_NAME") 
                //       + "  PK " + rs.getString("PKTABLE_NAME")+ "." + rs.getString("PKCOLUMN_NAME")); 
               String pkTableName = rs.getString("FKTABLE_NAME");
               //String fkColumnName = rs.getString("FKCOLUMN_NAME");
               TableNode pkTable = tableMap.get(pkTableName);
               if(pkTable == null){
                   pkTable = new TableNode(pkTableName);
                   tableMap.put(pkTableName, pkTable);
               }
               
                //System.out.println(" T "+t.tableName +"  <- "+pkTable.tableName);
               pkTable.children.add(t);
             }
             
         }
        
         return new ColumnTree(factTable, tableMap);
     }
     
     
     public Map<String, Integer> getTableColumns(final String tableName){
        Statement s  = null;
        ResultSet rs = null;
        Statement s2 = null;
        Map<String, Integer> columnNames = new HashMap<String, Integer>();
        try {
             s = con.createStatement(); 
             s2 = con.createStatement();
             rs = s.executeQuery("SHOW columns FROM "+tableName);
             rs.beforeFirst();
             while(rs.next()){
                 
                 if(rs.getString("key").isEmpty()){
                    final String column = rs.getString(1);
                    String columnTrim = column.contains(".") ? column.split("\\.")[1] : column;
                    columnTrim = columnTrim.contains(")") ? columnTrim.split("\\)")[0] : columnTrim;
                     
                    final ResultSet rs2 = s2.executeQuery("SELECT DISTINCT COUNT(" + columnTrim + ") FROM " + tableName);
                    rs2.next();
                    int count = rs2.getInt(1);
                    columnNames.put(columnTrim, count);
                 }
             }

        } catch (Exception ex) {        
            ex.printStackTrace();
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
            if(s2 != null){
                try {
                    s2.close();
                } catch (SQLException ex) {                
                }
            }
        } 
        
        return columnNames;
     }
     
     public List<Index> refreshIndexSelectivity(){
        Statement s  = null;
        Statement s2 = null;
        ResultSet rs = null;
        List<Index> indexes = new ArrayList<Index>();
        try {

             s = con.createStatement();
             s2 = con.createStatement();
             if(Constants.debug)
                 System.out.println("Refreshing index selectivity cache");
             
             s.executeUpdate("TRUNCATE TABLE Selectivity;");
             
             rs = s.executeQuery("show tables;");
             
             List<String> tableNames = new ArrayList<String>();
             rs.beforeFirst();
             while(rs.next()){
                 tableNames.add(rs.getString(1));
             }
             rs.close();
             
             for(String table: tableNames){
                 if(table.contains("datamart") || table.contains("counters"))
                     continue;
                 ResultSet rs1 = s.executeQuery("desc "+table+" ;");
                 rs1.beforeFirst();
                 while(rs1.next()){
                     String key = rs1.getString("KEY");
                     boolean isIndex = (key != null && Constants.allowedIndexes.contains(key));
                     if(isIndex){
                        String field = rs1.getString(1);
                        StringBuilder sb = new StringBuilder("select count(*) from ( select distinct `");
                        int select = 0;
                        sb.append(field);
                        sb.append("` FROM `");
                        sb.append(table);
                        sb.append("`) a;");
                        
                        //if(Constants.debug)
                            //System.out.println("Execute Query "+sb.toString());
                        ResultSet rs2 = s2.executeQuery(sb.toString());
                        rs2.beforeFirst();
                        if(rs2.next()){
                            select = rs2.getInt(1);
                        }
                        
                        sb = new StringBuilder("select count(*) ");
                        int select2 =0;
                        sb.append(" FROM `");
                        sb.append(table);
                        sb.append("` ;");
                        
                        //if(Constants.debug)
                           // System.out.println("Execute Query "+sb.toString());
                        rs2 = s2.executeQuery(sb.toString());
                        rs2.beforeFirst();
                        if(rs2.next()){
                            select2 = rs2.getInt(1);
                        }
                        
                        indexes.add(new Index((double)select/select2, table, field));
                        
                        sb = new StringBuilder("INSERT INTO Selectivity VALUES(\"");
                        sb.append(table);
                        sb.append("\",'");
                        sb.append(field);
                        sb.append("',");
                        sb.append((double)select/select2);
                        sb.append(");");
                        
                       // if(Constants.debug)
                           // System.out.println("Execute Update "+sb.toString());
                        int numUpdates = s2.executeUpdate(sb.toString());
                        if(Constants.debug && numUpdates != 1){
                            System.out.println("[ERROR] No column was updated for selectivity count");
                        }
                     }
                 }
            }
                 
        } catch (Exception ex) {        
            System.out.println("[ERROR] "+ex.getMessage());
            ex.printStackTrace();
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
            if(s2 != null){
                try {
                    s2.close();
                } catch (SQLException ex) {                
                }
            }
        }
        
        return indexes;
     }
     
     public List<String[]> getDatamart(String[] tables){
        Statement s  = null;
        ResultSet rs = null;
        List<String[]> datamartData = null;
        try {
             s = con.createStatement();
             if(Constants.debug)
                 System.out.println("Execute Query SELECT * FROM datamarts;");
             String datamartName;
             
             String tableNames = Util.join(tables, ",");
             
             rs = s.executeQuery(datamartSelectStr+tableNames+"';");
             
             rs.beforeFirst();
             if(rs.next()){
                 datamartName = rs.getString(1);
             }
             else{
                 return null;
             }
             
             BufferedReader br = new BufferedReader(new FileReader(Constants.datamartLocation));
             datamartData = new ArrayList<String[]>();
             String line;
             while((line = br.readLine()) != null){
                 datamartData.add(line.split(","));
             }
             
             br.close();
             
        } catch (Exception ex) {        
            System.out.println("[ERROR] "+ex.getMessage());
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
        } 
        
        return datamartData;
     }
    
     /**
      * Get column count information
      * 
      * @param table
      * @param column
      * @return Tuple(column_count_nr, column_sum_nr)
      */
     public Tuple<Long, Long> getCount(String table, String column){
        Statement s  = null;
        ResultSet rs = null;
        Tuple<Long, Long> pair = null;
        try {
             s = con.createStatement();
             
             StringBuilder sb = new StringBuilder("SELECT col_count, col_sum FROM counters WHERE table_name LIKE '");
             sb.append(table);
             sb.append("' AND column_name LIKE '");
             sb.append(column);
             sb.append("';");
             
             if(Constants.debug)
                 System.out.println("Execute Query "+sb.toString());
             rs = s.executeQuery(sb.toString());
             
             rs.beforeFirst();
             if(rs.next()){
                 pair = new Tuple<Long, Long>(rs.getLong(1), rs.getLong(2));
             }
                         
        } catch (Exception ex) {        
            ex.printStackTrace();
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
        }
        
        return pair;
    }
     
    public void calcCount(String table, String column){
        Statement s  = null;
        Statement us = null;
        ResultSet rs = null;
        try {
             s = con.createStatement();
             us = con.createStatement();
             
             StringBuilder sb = new StringBuilder("SELECT COUNT(");
             sb.append(column);
             sb.append("), SUM(");
             sb.append(column);
             sb.append(") FROM ");
             sb.append(table);
             sb.append(";");
             
             if(Constants.debug)
                 System.out.println("Execute Query "+sb.toString());
             rs = s.executeQuery(sb.toString());
             
             rs.beforeFirst();
             long count = 0, sum = 0;
             if(rs.next()){
                 count = rs.getLong(1);
                 sum = rs.getLong(2);
             }
             
             String updateStr = String.format("UPDATE counters SET col_count=%s, col_sum=%s "
                     + "WHERE table_name LIKE '%s' AND column_name LIKE '%s';",count, sum, table, column);
             
             if(Constants.debug)
                 System.out.println("Trying to update "+updateStr);
             
             if(true){
             
                 StringBuilder sb2 = new StringBuilder("INSERT INTO counters VALUES(NULL,'");
                 sb2.append(table);
                 sb2.append("',");
                 sb2.append(String.valueOf(count));
                 sb2.append(",");
                 sb2.append(String.valueOf(sum));
                 sb2.append(",'");
                 sb2.append(column);
                 sb2.append("');");
                 
                         
                 // System.out.println("Trying to update "+sb2.toString());
                 us.executeUpdate(sb2.toString());
             }
             
        } catch (Exception ex) {        
            ex.printStackTrace();
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
            
            if(us != null){
                try {
                    us.close();
                } catch (SQLException ex) {                
                }
            }
        } 
     }
    
    public String getShippingHistoricalInfo(){
        final StringBuilder sb = new StringBuilder();
        final String query = "SELECT YEAR(dateStart), COUNT(1) count, networth from shipment a, shipping_company b WHERE a.shipping_company_id = b.shipping_comp_info_id GROUP BY YEAR(dateStart);";
        Statement s  = null;
        ResultSet rs = null;
        try {
             s = con.createStatement();
             rs = s.executeQuery(query);
             rs.beforeFirst();
             while(rs.next()){
                 final int year = rs.getInt(1);
                 final int count = rs.getInt(2);
                 final int netWorth = rs.getInt(3);
                 sb.append(year);
                 sb.append(",");
                 sb.append(count);
                 sb.append(",");
                 sb.append("1");
                 sb.append(",");
                 sb.append(netWorth);
                 sb.append("\n");
             }
             
        } catch (Exception ex) {        
            ex.printStackTrace();
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) {                
                }
            }
            
        } 
        return sb.toString();
    }
    
    
    public Map<Integer, List<int[]>> getShippingHistoricalInfoList(){
        final Map<Integer, List<int[]>> map = new HashMap<Integer, List<int[]>>();
        final String query = "SELECT YEAR(dateStart),  b.idShipping_company, COUNT(1) count, networth from shipment a, shipping_company b "
                + "WHERE a.shipping_company_id = b.idShipping_company GROUP BY YEAR(dateStart) ORDER BY idShipping_company;";
        Statement s  = null;
        ResultSet rs = null;
        try {
             s = con.createStatement();
             rs = s.executeQuery(query);
             rs.beforeFirst();
             while(rs.next()){
                 final int year = rs.getInt(1);
                 final int shippingCompId = rs.getInt(2);
                 final int count = rs.getInt(3);
                 final int netWorth = rs.getInt(4);
                 int[] c = new int[3];
                 c[0] = year;
                 c[1] = count;
                 c[2] = netWorth;
                
                 List<int[]> list = map.get(shippingCompId);
                 if(list == null){
                     list = new ArrayList<int[]>();
                     map.put(shippingCompId, list);
                 }
  
                 list.add(c);
             }
             
        } catch (Exception ex) {        
            ex.printStackTrace();
        }
        finally{
            if(s != null){
                try {
                    s.close();
                } catch (SQLException ex) { 
                    ex.printStackTrace();
                }
            }
            
        } 
        
        return map;
    }
     
}